package com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.impl;

import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.entity.HouseCategoryArea;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.entity.OtherHouseInfo;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.OtherHouseInfoRepository;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/02/09
 * @Description: 其他房屋基础资料
 */
@Repository
public class OtherHouseInfoRepositoryImpl implements OtherHouseInfoRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "xczh_other_house_info"; // 表名
    private static final String TABLE_ID = "xgoi_id"; // 表id字段名

    @Override
    public void deleteByXlrmId(String xlrmId) {
        String sql = "delete from  "+TABLE_NAME+"  where xlrm_id= '"+xlrmId+"'" ;
        jdbcTemplate.update(sql);
    }

    @Override
    public void batchSave(List<OtherHouseInfo> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO "+TABLE_NAME +
                " ( xgoi_id,xlrm_id,fill_form_user,tel,fill_form_time,province,city,county," +
                "unit_name,affiliated_unit,fun,indoor_asset_avg," +
                "unit_price1,unit_price2,unit_price31,unit_price32,unit_price4,unit_price51,unit_price52,unit_price53,unit_price6,unit_price7," +
                "area1,area2,area31,area32,area4,area51,area52,area53,area6,area7)" +
                "values (:xgoiId,:xlrmId,:fillFormUser,:tel,:fillFormTime,:province,:city,:county," +
                ":unitName,:affiliatedUnit,:fun,:indoorAssetAvg," +
                ":unitPrice1,:unitPrice2,:unitPrice31,:unitPrice32,:unitPrice4,:unitPrice51,:unitPrice52,:unitPrice53,:unitPrice6,:unitPrice7," +
                ":area1,:area2,:area31,:area32,:area4,:area51,:area52,:area53,:area6,:area7)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<HouseCategoryArea> getDataByXsemId(String xsemId) {
        String sql = "SELECT province,city,county,housePurposeName, " +
                "SUM(v1Area) AS v1Area, " +
                "SUM(v2Area) AS v2Area, " +
                "SUM(v3Area) AS v3Area, " +
                "SUM(v4Area) AS v4Area, " +
                "SUM(v5Area) AS v5Area, " +
                "SUM(v6Area) AS v6Area, " +
                "SUM(v7Area) AS v7Area, " +
                "SUM(v8Area) AS v8Area, " +
                "SUM(v9Area) AS v9Area, " +
                "SUM(v10Area) AS v10Area " +
                "FROM (SELECT province,city,county, " +
                "CASE WHEN fun IN ('体育场馆和影剧院', '宾馆酒店写字楼', '金融') THEN '城市公用' " +
                "WHEN fun = '学校' THEN '教育系统' " +
                "WHEN fun = '医疗卫生' THEN '卫生系统' " +
                "WHEN fun IN ('工厂', '其他') THEN '其他房屋' " +
                "END AS housePurposeName, " +
                "    COALESCE(CAST(NULLIF(area1, '') AS DECIMAL(10, 2)), 0) AS v1Area, " +
                "    COALESCE(CAST(NULLIF(area2, '') AS DECIMAL(10, 2)), 0) AS v2Area, " +
                "    COALESCE(CAST(NULLIF(area31, '') AS DECIMAL(10, 2)), 0) AS v3Area, " +
                "    COALESCE(CAST(NULLIF(area32, '') AS DECIMAL(10, 2)), 0) AS v4Area, " +
                "    COALESCE(CAST(NULLIF(area4, '') AS DECIMAL(10, 2)), 0) AS v5Area, " +
                "    COALESCE(CAST(NULLIF(area51, '') AS DECIMAL(10, 2)), 0) AS v6Area, " +
                "    COALESCE(CAST(NULLIF(area52, '') AS DECIMAL(10, 2)), 0) AS v7Area, " +
                "    COALESCE(CAST(NULLIF(area53, '') AS DECIMAL(10, 2)), 0) AS v8Area, " +
                "    COALESCE(CAST(NULLIF(area6, '') AS DECIMAL(10, 2)), 0) AS v9Area, " +
                "    COALESCE(CAST(NULLIF(area7, '') AS DECIMAL(10, 2)), 0) AS v10Area " +
                "FROM xczh_other_house_info a " +
                "LEFT JOIN xczh_loss_report_manage b ON a.xlrm_id = b.id " +
                "WHERE b.xsem_id = ? ) AS subquery " +
                "GROUP BY province,city,county,housePurposeName;";
        List<HouseCategoryArea> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class),xsemId);
        return list;
    }
}
